/**
 * Project: complex.ibatis
 * 
 * File Created at 2010-3-30
 * $Id$
 * 
 * Copyright 2008 Alibaba.com Croporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Alibaba Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Alibaba.com.
 */
package com.ymkyve.complex.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 三种DB的 jdbc连接
 * 
 * @author wb_liang.gao
 */
public class JDBCConnection {

    public static void main(String[] args) {

        try {

            //oracle
            connectOracle();

            //mysql
            connectMysql();
            
            //derby
            connectDerby();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * @throws SQLException
     */
    private static void connectMysql() throws Exception {
        //mysql  (mysql可以没有class.forName)
        Class.forName("com.mysql.jdbc.Driver").newInstance();

        Properties props = new Properties();
        props.setProperty("user", "ymkyve_mysql");
        props.setProperty("password", "123456");
        //参数写法可以直接使用? &连接在url后面
        //Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ymkyve?user=ymkyve_mysql&password=123456");
        //url: jdbc:type:schema  
        //这里如果不带ymkyve,可以在sql中使用ymkyve.user  (即:jdbc:mysql://localhost:3306/   ymkyve.user)
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ymkyve", props);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from user");
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }

    /**
     * @throws SQLException
     */
    private static void connectOracle() throws Exception {
        //oracle
        /**class.forName*/
        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        
        /**Connection*/
        Properties props = new Properties();
        props.setProperty("user", "alp");
        props.setProperty("password", "alp");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.20.147.2:1521:TESTDB",
                props);
        /**Statement*/
        Statement stmt = conn.createStatement();
        
        /**ResultSet*/
        ResultSet rs = stmt.executeQuery("select * from alp_receiver t where t.id = '106'");
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }

    /**
     * @throws SQLException
     */
    private static void connectDerby() throws Exception {
        //oracle
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

        Properties props = new Properties();
        props.put("user", "user1");
        props.put("password", "user1");
        Connection conn = DriverManager.getConnection("jdbc:derby:helloDB;create=true", props);
        Statement s = conn.createStatement();
        s.execute("create table hellotable(name varchar(40), score int)");
        System.out.println("Created table hellotable");
        s.execute("insert into hellotable values('Ruth Cao', 86)");
        s.execute("insert into hellotable values ('Flora Shi', 92)");
        
        // list the two records
        ResultSet rs = s.executeQuery("SELECT name, score FROM hellotable ORDER BY score");
        while (rs.next()) {
            System.out.println(rs.getInt(2));
        }
        s.execute("drop table hellotable");
    }

}
